from flask import Flask, request, jsonify, make_response
from flask_sqlalchemy import SQLAlchemy
from flask_jwt_extended import JWTManager, jwt_required, create_access_token, get_jwt_identity
from datetime import datetime, timedelta
import re
import csv
from io import StringIO
import random
import mysql.connector
from mysql.connector import Error

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:password@localhost/bankdb'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['JWT_SECRET_KEY'] = 'banking-system-secret-key'
app.config['JWT_ACCESS_TOKEN_EXPIRES'] = timedelta(hours=24)
db = SQLAlchemy(app)
jwt = JWTManager(app)


# 数据模型定义
class Customer(db.Model):
    __tablename__ = 'customers'
    customer_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    customer_name = db.Column(db.String(50), nullable=False)
    pid = db.Column(db.String(18), nullable=False, unique=True)
    telephone = db.Column(db.String(11), nullable=False)
    address = db.Column(db.String(200))
    created_at = db.Column(db.TIMESTAMP, default=datetime.now)
    updated_at = db.Column(db.TIMESTAMP, default=datetime.now, onupdate=datetime.now)


class DepositType(db.Model):
    __tablename__ = 'deposit_types'
    saving_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    saving_name = db.Column(db.String(50), nullable=False)
    description = db.Column(db.String(200))
    created_at = db.Column(db.TIMESTAMP, default=datetime.now)


class BankCard(db.Model):
    __tablename__ = 'bank_cards'
    card_id = db.Column(db.String(16), primary_key=True)
    currency = db.Column(db.String(10), default='RMB')
    open_date = db.Column(db.Date, default=datetime.now().date())
    open_money = db.Column(db.DECIMAL(10, 2), nullable=False)
    balance = db.Column(db.DECIMAL(10, 2), nullable=False)
    password = db.Column(db.String(6), default='888888')
    is_report_loss = db.Column(db.Boolean, default=False)
    customer_id = db.Column(db.Integer, db.ForeignKey('customers.customer_id'), nullable=False)
    saving_id = db.Column(db.Integer, db.ForeignKey('deposit_types.saving_id'), nullable=False)
    created_at = db.Column(db.TIMESTAMP, default=datetime.now)
    updated_at = db.Column(db.TIMESTAMP, default=datetime.now, onupdate=datetime.now)


class Transaction(db.Model):
    __tablename__ = 'transactions'
    trans_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    trade_date = db.Column(db.DATETIME, default=datetime.now)
    trade_type = db.Column(db.Enum('deposit', 'withdrawal', 'transfer'), nullable=False)
    trade_money = db.Column(db.DECIMAL(10, 2), nullable=False)
    remark = db.Column(db.String(200))
    card_id = db.Column(db.String(16), db.ForeignKey('bank_cards.card_id'), nullable=False)
    related_card_id = db.Column(db.String(16), db.ForeignKey('bank_cards.card_id'))
    created_at = db.Column(db.TIMESTAMP, default=datetime.now)


# 认证路由
@app.route('/api/auth/login', methods=['POST'])
def login():
    data = request.get_json()
    username = data.get('username', None)
    password = data.get('password', None)

    # 实际项目中应从数据库查询用户
    if username == 'admin' and password == 'admin123':
        access_token = create_access_token(identity=username)
        return jsonify(access_token=access_token), 200

    return jsonify({"msg": "Bad username or password"}), 401


# 客户管理API（续）
@app.route('/api/customers/<int:customer_id>', methods=['GET'])
@jwt_required()
def get_customer(customer_id):
    customer = Customer.query.get_or_404(customer_id)

    return jsonify({
        "customer_id": customer.customer_id,
        "customer_name": customer.customer_name,
        "pid": customer.pid,
        "telephone": customer.telephone,
        "address": customer.address,
        "created_at": customer.created_at.strftime('%Y-%m-%d %H:%M:%S'),
        "updated_at": customer.updated_at.strftime('%Y-%m-%d %H:%M:%S')
    }), 200


@app.route('/api/customers/<int:customer_id>', methods=['DELETE'])
@jwt_required()
def delete_customer(customer_id):
    customer = Customer.query.get_or_404(customer_id)

    # 检查是否有银行卡关联
    if customer.cards:
        return jsonify({"error": "该客户关联了银行卡，无法删除"}), 400

    db.session.delete(customer)
    try:
        db.session.commit()
        return jsonify({"message": "客户删除成功"}), 200
    except Exception as e:
        db.session.rollback()
        return jsonify({"error": str(e)}), 500


@app.route('/api/customers/export', methods=['GET'])
@jwt_required()
def export_customers():
    customers = Customer.query.all()

    output = StringIO()
    writer = csv.writer(output)
    writer.writerow(['客户编号', '客户姓名', '身份证号', '联系电话', '联系地址', '创建时间'])

    for customer in customers:
        writer.writerow([
            customer.customer_id,
            customer.customer_name,
            customer.pid,
            customer.telephone,
            customer.address or '',
            customer.created_at.strftime('%Y-%m-%d %H:%M:%S')
        ])

    response = make_response(output.getvalue())
    response.headers["Content-Disposition"] = "attachment; filename=customers.csv"
    response.headers["Content-Type"] = "text/csv"
    return response


# 银行卡管理API
@app.route('/api/cards', methods=['POST'])
@jwt_required()
def create_card():
    data = request.get_json()

    # 验证输入
    if not data.get('customer_id'):
        return jsonify({"error": "客户编号不能为空"}), 400

    if not data.get('saving_id'):
        return jsonify({"error": "存款类型编号不能为空"}), 400

    if not data.get('open_money') or float(data.get('open_money')) < 1:
        return jsonify({"error": "开户金额不能小于1元"}), 400

    # 检查客户是否存在
    customer = Customer.query.get(data.get('customer_id'))
    if not customer:
        return jsonify({"error": "客户不存在"}), 400

    # 检查存款类型是否存在
    deposit_type = DepositType.query.get(data.get('saving_id'))
    if not deposit_type:
        return jsonify({"error": "存款类型不存在"}), 400

    # 生成随机卡号（前8位固定，后8位随机）
    random_part = ''.join([str(random.randint(0, 9)) for _ in range(8)])
    card_id = '10103576' + random_part

    # 创建银行卡
    card = BankCard(
        card_id=card_id,
        open_money=data.get('open_money'),
        balance=data.get('open_money'),
        customer_id=data.get('customer_id'),
        saving_id=data.get('saving_id'),
        password=data.get('password', '888888')
    )

    db.session.add(card)
    try:
        db.session.commit()
        return jsonify({
            "message": "银行卡创建成功",
            "data": {
                "card_id": card.card_id,
                "open_date": card.open_date.strftime('%Y-%m-%d'),
                "balance": str(card.balance),
                "saving_name": deposit_type.saving_name
            }
        }), 201
    except Exception as e:
        db.session.rollback()
        return jsonify({"error": str(e)}), 500


@app.route('/api/cards', methods=['GET'])
@jwt_required()
def get_cards():
    # 支持查询参数：卡号、客户姓名、是否挂失
    card_id = request.args.get('card_id', '')
    customer_name = request.args.get('customer_name', '')
    is_lost = request.args.get('is_lost', '')

    query = BankCard.query

    if card_id:
        query = query.filter(BankCard.card_id.like(f'%{card_id}%'))

    if customer_name:
        query = query.join(Customer).filter(Customer.customer_name.like(f'%{customer_name}%'))

    if is_lost.lower() in ['true', 'false']:
        query = query.filter(BankCard.is_report_loss == (is_lost.lower() == 'true'))

    cards = query.all()

    result = []
    for card in cards:
        result.append({
            "card_id": card.card_id,
            "currency": card.currency,
            "open_date": card.open_date.strftime('%Y-%m-%d'),
            "open_money": str(card.open_money),
            "balance": str(card.balance),
            "is_lost": card.is_report_loss,
            "customer_name": card.customer.customer_name,
            "saving_name": card.deposit_type.saving_name
        })

    return jsonify({
        "total": len(result),
        "data": result
    }), 200


@app.route('/api/cards/<string:card_id>', methods=['GET'])
@jwt_required()
def get_card(card_id):
    card = BankCard.query.get_or_404(card_id)

    return jsonify({
        "card_id": card.card_id,
        "currency": card.currency,
        "open_date": card.open_date.strftime('%Y-%m-%d'),
        "open_money": str(card.open_money),
        "balance": str(card.balance),
        "is_lost": card.is_report_loss,
        "password": card.password,
        "customer_id": card.customer_id,
        "customer_name": card.customer.customer_name,
        "saving_id": card.saving_id,
        "saving_name": card.deposit_type.saving_name
    }), 200


@app.route('/api/cards/<string:card_id>', methods=['PUT'])
@jwt_required()
def update_card(card_id):
    card = BankCard.query.get_or_404(card_id)
    data = request.get_json()

    if 'password' in data:
        if len(data['password']) != 6 or not data['password'].isdigit():
            return jsonify({"error": "密码必须为6位数字"}), 400
        card.password = data['password']

    if 'is_report_loss' in data:
        card.is_report_loss = data['is_report_loss']

    try:
        db.session.commit()
        return jsonify({"message": "银行卡信息更新成功"}), 200
    except Exception as e:
        db.session.rollback()
        return jsonify({"error": str(e)}), 500


@app.route('/api/cards/<string:card_id>', methods=['DELETE'])
@jwt_required()
def delete_card(card_id):
    card = BankCard.query.get_or_404(card_id)

    # 检查余额是否大于1元
    if card.balance > 1:
        return jsonify({"error": "账户余额大于1元，无法删除"}), 400

    db.session.delete(card)
    try:
        db.session.commit()
        return jsonify({"message": "银行卡删除成功"}), 200
    except Exception as e:
        db.session.rollback()
        return jsonify({"error": str(e)}), 500


@app.route('/api/cards/export', methods=['GET'])
@jwt_required()
def export_cards():
    cards = BankCard.query.all()

    output = StringIO()
    writer = csv.writer(output)
    writer.writerow([
        '卡号', '币种', '开户日期', '开户金额', '余额',
        '密码', '是否挂失', '客户编号', '客户姓名', '存款类型'
    ])

    for card in cards:
        writer.writerow([
            card.card_id,
            card.currency,
            card.open_date.strftime('%Y-%m-%d'),
            str(card.open_money),
            str(card.balance),
            card.password,
            '是' if card.is_report_loss else '否',
            card.customer_id,
            card.customer.customer_name,
            card.deposit_type.saving_name
        ])

    response = make_response(output.getvalue())
    response.headers["Content-Disposition"] = "attachment; filename=cards.csv"
    response.headers["Content-Type"] = "text/csv"
    return response


# 存款类型管理API
@app.route('/api/deposits', methods=['POST'])
@jwt_required()
def create_deposit_type():
    data = request.get_json()

    if not data.get('saving_name'):
        return jsonify({"error": "存款类型名称不能为空"}), 400

    # 检查名称是否已存在
    if DepositType.query.filter_by(saving_name=data.get('saving_name')).first():
        return jsonify({"error": "该存款类型名称已存在"}), 400

    deposit_type = DepositType(
        saving_name=data.get('saving_name'),
        description=data.get('description', '')
    )

    db.session.add(deposit_type)
    try:
        db.session.commit()
        return jsonify({
            "message": "存款类型创建成功",
            "data": {
                "saving_id": deposit_type.saving_id,
                "saving_name": deposit_type.saving_name,
                "description": deposit_type.description
            }
        }), 201
    except Exception as e:
        db.session.rollback()
        return jsonify({"error": str(e)}), 500


@app.route('/api/deposits', methods=['GET'])
@jwt_required()
def get_deposit_types():
    deposit_types = DepositType.query.all()

    return jsonify({
        "total": len(deposit_types),
        "data": [
            {
                "saving_id": dt.saving_id,
                "saving_name": dt.saving_name,
                "description": dt.description
            }
            for dt in deposit_types
        ]
    }), 200


@app.route('/api/deposits/export', methods=['GET'])
@jwt_required()
def export_deposit_types():
    deposit_types = DepositType.query.all()

    output = StringIO()
    writer = csv.writer(output)
    writer.writerow(['存款类型编号', '存款类型名称', '描述'])

    for dt in deposit_types:
        writer.writerow([dt.saving_id, dt.saving_name, dt.description or ''])

    response = make_response(output.getvalue())
    response.headers["Content-Disposition"] = "attachment; filename=deposit_types.csv"
    response.headers["Content-Type"] = "text/csv"
    return response


# 交易管理API
@app.route('/api/transactions', methods=['GET'])
@jwt_required()
def get_transactions():
    card_id = request.args.get('card_id', '')
    trade_type = request.args.get('trade_type', '')
    start_date = request.args.get('start_date', '')
    end_date = request.args.get('end_date', '')

    query = Transaction.query

    if card_id:
        query = query.filter(Transaction.card_id.like(f'%{card_id}%'))

    if trade_type:
        query = query.filter(Transaction.trade_type == trade_type)

    if start_date:
        query = query.filter(Transaction.trade_date >= start_date)

    if end_date:
        query = query.filter(Transaction.trade_date <= end_date)

    transactions = query.all()

    result = []
    for trans in transactions:
        related_card = None
        if trans.related_card_id:
            related_card = BankCard.query.get(trans.related_card_id)

        result.append({
            "trans_id": trans.trans_id,
            "trade_date": trans.trade_date.strftime('%Y-%m-%d %H:%M:%S'),
            "trade_type": trans.trade_type,
            "trade_money": str(trans.trade_money),
            "remark": trans.remark,
            "card_id": trans.card_id,
            "customer_name": trans.card.customer.customer_name,
            "related_card_id": trans.related_card_id,
            "related_customer": related_card.customer.customer_name if related_card else None
        })

    return jsonify({
        "total": len(result),
        "data": result
    }), 200


@app.route('/api/transactions/export', methods=['GET'])
@jwt_required()
def export_transactions():
    transactions = Transaction.query.all()

    output = StringIO()
    writer = csv.writer(output)
    writer.writerow([
        '交易ID', '交易日期', '交易类型', '交易金额',
        '备注', '卡号', '客户姓名', '关联卡号'
    ])

    for trans in transactions:
        related_customer = ''
        if trans.related_card_id:
            related_card = BankCard.query.get(trans.related_card_id)
            related_customer = related_card.customer.customer_name if related_card else ''

        writer.writerow([
            trans.trans_id,
            trans.trade_date.strftime('%Y-%m-%d %H:%M:%S'),
            trans.trade_type,
            str(trans.trade_money),
            trans.remark,
            trans.card_id,
            trans.card.customer.customer_name,
            related_customer
        ])

    response = make_response(output.getvalue())
    response.headers["Content-Disposition"] = "attachment; filename=transactions.csv"
    response.headers["Content-Type"] = "text/csv"
    return response


# 客户模块API
@app.route('/api/card/balance/<string:card_id>', methods=['GET'])
def get_balance(card_id):
    card = BankCard.query.get_or_404(card_id)

    return jsonify({
        "card_id": card.card_id,
        "balance": str(card.balance),
        "is_lost": card.is_report_loss
    }), 200


@app.route('/api/card/deposit', methods=['POST'])
def deposit():
    data = request.get_json()

    card_id = data.get('card_id')
    amount = data.get('amount')
    password = data.get('password')

    if not card_id or not amount or not password:
        return jsonify({"error": "卡号、金额和密码不能为空"}), 400

    try:
        amount = float(amount)
        if amount <= 0:
            return jsonify({"error": "存款金额必须大于0"}), 400
    except:
        return jsonify({"error": "金额格式不正确"}), 400

    card = BankCard.query.get_or_404(card_id)

    if card.is_report_loss:
        return jsonify({"error": "卡片已挂失，无法操作"}), 400

    if card.password != password:
        return jsonify({"error": "密码错误"}), 400

    # 更新余额（触发器会自动记录交易）
    card.balance += amount
    db.session.commit()

    return jsonify({
        "message": "存款成功",
        "card_id": card_id,
        "new_balance": str(card.balance),
        "amount": str(amount)
    }), 200


@app.route('/api/card/withdraw', methods=['POST'])
def withdraw():
    data = request.get_json()

    card_id = data.get('card_id')
    amount = data.get('amount')
    password = data.get('password')

    if not card_id or not amount or not password:
        return jsonify({"error": "卡号、金额和密码不能为空"}), 400

    try:
        amount = float(amount)
        if amount <= 0:
            return jsonify({"error": "取款金额必须大于0"}), 400
    except:
        return jsonify({"error": "金额格式不正确"}), 400

    card = BankCard.query.get_or_404(card_id)

    if card.is_report_loss:
        return jsonify({"error": "卡片已挂失，无法操作"}), 400

    if card.password != password:
        return jsonify({"error": "密码错误"}), 400

    if card.balance < amount + 1:
        return jsonify({"error": "余额不足，当前余额：" + str(card.balance)}), 400

    # 更新余额（触发器会自动记录交易）
    card.balance -= amount
    db.session.commit()

    return jsonify({
        "message": "取款成功",
        "card_id": card_id,
        "new_balance": str(card.balance),
        "amount": str(amount)
    }), 200


@app.route('/api/card/transfer', methods=['POST'])
def transfer():
    data = request.get_json()

    from_card_id = data.get('from_card_id')
    to_card_id = data.get('to_card_id')
    amount = data.get('amount')
    password = data.get('password')
    remark = data.get('remark', '转账交易')

    if not from_card_id or not to_card_id or not amount or not password:
        return jsonify({"error": "转出卡号、转入卡号、金额和密码不能为空"}), 400

    try:
        amount = float(amount)
        if amount <= 0:
            return jsonify({"error": "转账金额必须大于0"}), 400
    except:
        return jsonify({"error": "金额格式不正确"}), 400

    # 调用存储过程实现转账
    try:
        conn = mysql.connector.connect(
            host="localhost",
            user="root",
            password="password",
            database="bankdb"
        )
        cursor = conn.cursor()

        success = False
        message = ""

        cursor.callproc('sp_transfer', [from_card_id, to_card_id, amount, password, remark, success, message])

        # 获取存储过程输出参数
        for result in cursor.stored_results():
            pass  # 存储过程无返回结果集

        # 从参数中获取结果
        cursor.execute("SELECT @_sp_transfer_5, @_sp_transfer_6")
        result = cursor.fetchone()
        success = result[0]
        message = result[1]

        conn.commit()
        cursor.close()
        conn.close()

        if success:
            return jsonify({
                "message": message,
                "from_card_id": from_card_id,
                "to_card_id": to_card_id,
                "amount": str(amount)
            }), 200
        else:
            return jsonify({"error": message}), 400

    except Error as e:
        return jsonify({"error": f"转账失败: {str(e)}"}), 500


@app.route('/api/card/loss', methods=['PUT'])
def report_loss():
    data = request.get_json()

    card_id = data.get('card_id')
    password = data.get('password')

    if not card_id or not password:
        return jsonify({"error": "卡号和密码不能为空"}), 400

    card = BankCard.query.get_or_404(card_id)

    if card.password != password:
        return jsonify({"error": "密码错误"}), 400

    card.is_report_loss = True
    db.session.commit()

    return jsonify({
        "message": "挂失成功",
        "card_id": card_id
    }), 200


@app.route('/api/card/change-password', methods=['PUT'])
def change_password():
    data = request.get_json()

    card_id = data.get('card_id')
    old_password = data.get('old_password')
    new_password = data.get('new_password')

    if not card_id or not old_password or not new_password:
        return jsonify({"error": "卡号、原密码和新密码不能为空"}), 400

    if len(new_password) != 6 or not new_password.isdigit():
        return jsonify({"error": "新密码必须为6位数字"}), 400

    card = BankCard.query.get_or_404(card_id)

    if card.password != old_password:
        return jsonify({"error": "原密码错误"}), 400

    card.password = new_password
    db.session.commit()

    return jsonify({
        "message": "密码修改成功",
        "card_id": card_id
    }), 200


# 视图查询API
@app.route('/api/view/customer-cards', methods=['GET'])
@jwt_required()
def get_customer_cards_view():
    # 使用原生SQL查询视图
    try:
        conn = mysql.connector.connect(
            host="localhost",
            user="root",
            password="password",
            database="bankdb"
        )
        cursor = conn.cursor(dictionary=True)
        cursor.execute("SELECT * FROM v_customer_cards")
        result = cursor.fetchall()
        cursor.close()
        conn.close()

        return jsonify({
            "total": len(result),
            "data": result
        }), 200

    except Error as e:
        return jsonify({"error": str(e)}), 500


if __name__ == '__main__':
    app.run(debug=True)